
begin tran

declare @i int 
select @i = convert(int, var_data) 
from igs_parm with(updlock) where var_name = 'gv_upd_id'
 
 select vip_no, min(trx_date) trx_date into #a 
from pos_sales_hdr 
where vip_no <> ''  
 --shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH')
 group by vip_no

 select a.vip_no, min(a.shop_code) shop_code into #b 
from pos_sales_hdr a join #a b on a.vip_no = b.vip_no and a.trx_date = b.trx_date
group by a.vip_no


update pos_vip_info set shop_code = a.shop_code, upd_id = @i
from #b a
where pos_vip_info.vip_no = a.vip_no and (pos_vip_info.shop_code = '' or pos_vip_info.shop_code <> a.shop_code)

--select * from pos_vip_info a where exists(select * from #b aa where a.vip_no = aa.vip_no and a.shop_code <> aa.shop_code)
select * from pos_vip_info a where not exists(select * from pos_shop aa where a.shop_code = aa.shop_code) and effect_year>=2015

rollback tran


begin tran

declare @i int 
select @i = convert(int, var_data) 
from igs_parm with(updlock) where var_name = 'gv_upd_id'

declare @vip_no nvarchar(20)

declare cu cursor  for select vip_no from pos_vip_info where shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') 
open cu 
fetch  next from cu into @vip_no
while @@fetch_status=0
begin
	exec sp_pos_recal_vip_pts @vip_no, @i 
fetch  next  from cu into @vip_no
end
close cu
deallocate cu
rollback tran



select vip_no, shop_code into #aa from pos_vip_info where shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and effect_year = 2015
select vip_no, shop_code into #bb from pos_vip_info where shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and effect_year = 2016
select vip_no, shop_code into #cc from pos_vip_info where shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and effect_year = 2017

select sum(org_amt), sum(equ_amt) 
from pos_sales_pay a join pos_sales_hdr b on a.trx_no = b.trx_no
join #aa c on b.shop_code = c.shop_code and b.vip_no = c.vip_no
where b.shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and 
	trx_date between '2015.01.01' and '2015.12.31' and 
	a.pay_type = 6

	select sum(org_amt), sum(equ_amt) 
from pos_sales_pay a join pos_sales_hdr b on a.trx_no = b.trx_no
join #bb c on b.shop_code = c.shop_code and b.vip_no = c.vip_no
where b.shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and 
	trx_date between '2016.01.01' and '2016.12.31' and 
	a.pay_type = 6

select sum(org_amt), sum(equ_amt) 
from pos_sales_pay a join pos_sales_hdr b on a.trx_no = b.trx_no
join #cc c on b.shop_code = c.shop_code and b.vip_no = c.vip_no
where b.shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and 
	trx_date between '2017.01.01' and '2017.12.31' and 
	a.pay_type = 6


select sum(vip_pts) from pos_vip_tran a join #aa b on a.shop_code = b.shop_code and a.vip_no = b.vip_no
where a.shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and 
	trx_date between '2015.01.01' and '2015.12.31' and trx_type = 'N'
	
	select sum(vip_pts) from pos_vip_tran a join #bb b on a.shop_code = b.shop_code and a.vip_no = b.vip_no
where a.shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and 
	trx_date between '2016.01.01' and '2016.12.31' and trx_type = 'N'

	select sum(vip_pts) from pos_vip_tran a join #cc b on a.shop_code = b.shop_code and a.vip_no = b.vip_no
where a.shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and 
	trx_date between '2017.01.01' and '2017.12.31' and trx_type = 'N'
	 
	select vip_no, count(distinct trx_no) cnt   
	 from pos_vip_tran where trx_date between  '2015.01.01'  and '2017.12.31'
	 and vip_pts > 0 and trx_type = 'N' and shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH')
	 group by vip_no
	 having count(distinct trx_no) > 1

	 	select sum(vip_pts) from pos_vip_tran a join #bb b on a.shop_code = b.shop_code and a.vip_no = b.vip_no
where a.shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and 
	trx_date between '2016.01.01' and '2016.12.31' and trx_type = 'N'



	select a.*
from pos_sales_pay a join pos_sales_hdr b on a.trx_no = b.trx_no
join #aa c on b.shop_code = c.shop_code and b.vip_no = c.vip_no
where b.shop_code in('HCML', '1218W', 'HKML', 'HKLCX', 'HKLP', 'HKKR', '1218NT', '1218TH') and 
	trx_date between '2015.01.01' and '2015.12.31' and 
	a.pay_type = 6